﻿using System;
using System.Collections.Generic;
using KPIS.DBM;
using System.Configuration;
using KPIS.GERP.GAMS.MODEL;
using System.Data;

namespace KPIS.GERP.GAMS.DAL
{
    public class D_ACM_GENERATE_ACCOUNT_Auto
    {
        DBManager dbManager = new DBManager(SystemInfo._DataProvider, ConfigurationSettings.AppSettings["ConnectionString"].ToString());
        ConvertNullable cn = new ConvertNullable();

        public IList<M_ACM_GENERATE_ACCOUNT_Auto> GetListMaster(string bgm_source_seq, string work_area_seq, string ref_type)
        {
            Nullable<int> nbgm_source_seq = cn.ConvertNullInt(bgm_source_seq);
            Nullable<int> nwork_area_seq = cn.ConvertNullInt(work_area_seq);
            //Nullable<int> nwork_area_seq = 1;
            DataTable drc = null;
            IList<M_ACM_GENERATE_ACCOUNT_Auto> infoList = new List<M_ACM_GENERATE_ACCOUNT_Auto>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);
                //dbManager.AddParameters(0, "@ref_type", info.ref_type);
                dbManager.AddParameters(0, "@bgm_source_seq", nbgm_source_seq);
                dbManager.AddParameters(1, "@work_area_seq", 1);
                dbManager.AddParameters(2, "@DeleteStatus", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(3, "@ApproveStatus", SystemInfo._ApproveRecordStatus);

                string strSQL =
                #region petition
                    // petition จ่ายผ่านส่วนราชการ =========================================================================
                    "SELECT * FROM"
                    + " (SELECT 'fim_petitions' as ref_type"
                    + ", 'G' as tran_type"
                    + ", pt.ref_no"
                    + ", pt.petition_date as tran_date"
                    + ", concat('รายการขอเบิกเลขที่ ', coalesce(pt.ref_no, '')) as tran_name"
                    + ", 1 as work_area_seq"
                    + ", pt.bgm_source_seq"
                    + ", pt.petition_seq as ref_seq"
                    + ", pt.bgm_year"
                    + ", null as year_no, null as month_no"

                    + " FROM fim_petitions pt"
                    + " WHERE pt.record_status = @ApproveStatus"
                    + " AND pt.tran_grp_seq is null"
                    + " AND pt.direct_pay = '0'"

                    // petition จ่ายตรง =========================================================================
                    + " union all "

                    + " SELECT 'fim_petitions' as ref_type"
                    + ", 'G' as tran_type"
                    + ", pt.ref_no"
                    + ", pt.petition_date as tran_date"
                    + ", concat('รายการขอเบิกเลขที่ ', coalesce(pt.ref_no, '')) as tran_name"
                    + ", 1 as work_area_seq"
                    + ", pt.bgm_source_seq"
                    + ", pt.petition_seq as ref_seq"
                    + ", pt.bgm_year"
                    + ", null as year_no, null as month_no"
                    //+ " , concat(coalesce(bgm_source_code, ''), ' : ', coalesce(bgm_source_name, ''))  as source_name "
                    //+ " , concat(coalesce(work_area_id, ''), ' : ', coalesce(work_area_name, ''))  as area_name "
                    //+ " , '2' as dtls_type"
                    + " FROM fim_petitions pt "
                    + " LEFT JOIN bgm_mas_source bgm_source ON "
                        + " pt.bgm_source_seq = bgm_source.bgm_source_seq "
                    + " LEFT JOIN hrm_mp_work_areas work_areas ON "
                        + " 1 = work_areas.work_area_seq "
                    + " WHERE pt.record_status = 'A' "
                    + " AND pt.tran_grp_seq is null  AND pt.direct_pay = '1' "
                #endregion
                #region money_request
                    // money_request =========================================================================
                    + " union all"

                    + " SELECT 'fim_money_requests' as ref_type"
                    + ", 'G' as tran_type"
                    + ", mr.ref_no"
                    + ", mr.money_req_date as tran_date"
                    + ", mr.money_req_name as tran_name"
                    + ", 1 as work_area_seq"
                    + ", mr.bgm_source_seq"
                    + ", mr.money_req_seq as ref_seq"
                    + ", mr.bgm_year"
                    + ", null as year_no, null as month_no"

                    + " FROM fim_money_requests mr"

                    + " WHERE mr.record_status = 'A'"
                    + " AND mr.tran_grp_seq is null"
                    + " AND mr.event_seq not in (16, 18)"
                    + " AND mr.use_advance <> 1"
                    + (!string.IsNullOrEmpty(bgm_source_seq) ? " AND mr.bgm_source_seq = @bgm_source_seq" : "")
                    //+ (!string.IsNullOrEmpty(work_area_seq) ? " AND mr.work_area_seq = @work_area_seq" : "")
                #endregion
                #region fim_payments
                    // payment =========================================================================
                    + " union all "// จ่ายเงินสด และโอน

                    + " SELECT 'fim_payments' as ref_type"
                    + ", 'P' as tran_type"
                    + ", pay.ref_no"
                    + ", pay.real_pay_date as tran_date"
                    + ", pay.pay_name as tran_name"
                    + ", 1 as work_area_seq"
                    + ", pay.bgm_source_seq"
                    + ", pay.pay_seq as ref_seq"
                    + ", pay.bgm_year"
                    + ", null as year_no, null as month_no"

                    + " FROM fim_payments pay"
                    + " left join fim_money_requests mr on pay.money_req_seq = mr.money_req_seq and mr.record_status = 'A'"
                    + " WHERE pay.record_status = 'A'"
                    + " AND pay.tran_grp_seq is null"
                    //+ " AND (pet.direct_pay is null OR pet.direct_pay = '0')"
                    + " AND pay.method_seq in (1, 3)"
                    + " AND pay.real_pay_date is not null"
                    + " AND pay.event_seq <> 19"
                    + " AND coalesce(mr.use_advance, 0) <> 1"
                    + (!string.IsNullOrEmpty(bgm_source_seq) ? " AND pay.bgm_source_seq = @bgm_source_seq" : "")
                    //+ (!string.IsNullOrEmpty(work_area_seq) ? " AND pay.work_area_seq = @work_area_seq" : "")

                    + " union all "// จ่ายเช็ค

                    + @"
SELECT 'fim_payments' as ref_type
, 'P' as tran_type
, pay.ref_no
, cheq.pay_date as tran_date
, pay.pay_name + '[ เช็คเลขที่ '+ c.cheque_no + ' ]' as tran_name
, 1 as work_area_seq
, pay.bgm_source_seq
, pay.pay_seq as ref_seq
, pay.bgm_year
, null as year_no, null as month_no
FROM fim_payments pay
left join (
	select pc.pay_seq, STUFF((
		SELECT N', ' + CAST(c.cheque_no AS VARCHAR(255))
		from fim_payment_cheques f1
		left join fim_cheques c on f1.cheque_seq = c.cheque_seq
		WHERE pc.pay_seq = f1.pay_seq
			and pc.record_status <> 'D'
			and c.record_status <> 'D'
			and c.cheque_status <> '0'
		FOR XML PATH ('')), 1, 2, '') AS cheque_no
	from fim_payment_cheques pc
	where pc.record_status <> 'D'

	GROUP BY pc.pay_seq, pc.record_status
) c on c.pay_seq = pay.pay_seq
left join fim_payment_cheques pc on pay.pay_seq = pc.pay_seq
left join fim_cheques cheq on pc.cheque_seq = cheq.cheque_seq
left join fim_money_requests mr on pay.money_req_seq = mr.money_req_seq and mr.record_status = 'A'
WHERE pay.record_status = 'A'
AND pay.tran_grp_seq is null
AND pay.method_seq in (2)
AND cheq.pay_date is not null
AND pay.event_seq <> 19
AND coalesce(mr.use_advance, 0) <> 1
"
                    + (!string.IsNullOrEmpty(bgm_source_seq) ? " AND pay.bgm_source_seq = @bgm_source_seq" : "")

                    + " union all "// จ่าย pay in pack

                    + @"
SELECT 'fim_payments' as ref_type
, 'P' as tran_type
, pay.ref_no
, pip.pip_date as tran_date
, pay.pay_name + ' [pip : ' + pip.pip_no + ']' as tran_name
, 1 as work_area_seq
, pay.bgm_source_seq
, pay.pay_seq as ref_seq
, pay.bgm_year
, null as year_no, null as month_no
FROM fim_payments pay
left join fim_payments_payinpack pip on pay.pay_seq = pip.pay_seq
    and pip.record_status <> 'D'
left join fim_money_requests mr on pay.money_req_seq = mr.money_req_seq and mr.record_status = 'A'
WHERE pay.record_status = 'A'
AND pay.tran_grp_seq is null
AND pay.method_seq in (4)
AND pip.pip_date is not null
AND pay.event_seq <> 19
AND coalesce(mr.use_advance, 0) <> 1
"
                    //+ " AND pay.method_seq = 2"
                    //+ " AND c.pay_date is not null"
                    //+ " AND c.cheque_status = 2"
                    + (!string.IsNullOrEmpty(bgm_source_seq) ? " AND pay.bgm_source_seq = @bgm_source_seq" : "")
                    //+ (!string.IsNullOrEmpty(work_area_seq) ? " AND pay.work_area_seq = @work_area_seq" : "")
                #endregion
                #region fim_receives
                    // receive =========================================================================
                    + " union all "

                    + " SELECT 'fim_receives' as ref_type"
                    + ", 'R' as tran_type"
                    + ", r.ref_no"
                    + ", r.receive_date as tran_date"
                    + ", case receive_mode when 1 then concat('รับเงินเบิกเกินส่งคืน ใบเสร็จเลขที่ ', coalesce(r.receipt_no, ''))"
                        + " when 2 then concat('รายการรับเงิน ใบเสร็จเลขที่ ', coalesce(r.receipt_no, ''))"
                        + " when 3 then concat('รับเงินจากรายการขอเบิก เลขที่ ', coalesce(pet.petition_no, ''))"
                        + " end as tran_name"
                    + ", 1 as work_area_seq"
                    + ", r.bgm_source_seq"
                    + ", r.receive_seq as ref_seq"
                    + ", r.bgm_year"
                    + ", null as year_no, null as month_no"

                    + " FROM fim_receives r"
                    + " LEFT JOIN fim_petitions pet on r.petition_seq = pet.petition_seq"
                    + " LEFT JOIN fim_money_requests mr on r.money_req_seq = mr.money_req_seq"
                    + " WHERE r.record_status not in ('D', 'C')"
                    + " and (COALESCE(r.receipt_no, '') not like '' or receive_mode = '3')"
                    + " AND r.tran_grp_seq is null"
                    + " AND coalesce(mr.use_advance, 0) <> 1"
                    + (!string.IsNullOrEmpty(bgm_source_seq) ? " AND r.bgm_source_seq = @bgm_source_seq" : "")
                    //+ (!string.IsNullOrEmpty(work_area_seq) ? " AND r.work_area_seq = @work_area_seq" : "")
                #endregion
                #region fim_money_returns
                    // return =========================================================================
                    + " union all "

                    + " SELECT 'fim_money_returns' as ref_type"
                    + ", 'G' as tran_type"
                    + ", ret.ref_no"
                    + ", ret.return_date as tran_date"
                    + ", concat('รายการหักล้าง ', coalesce(mr.money_req_name, '')) as tran_name"
                    + ", mr.work_area_seq"
                    + ", mr.bgm_source_seq"
                    + ", ret.return_seq as ref_seq"
                    + ", mr.bgm_year"
                    + ", null as year_no, null as month_no"

                    + " FROM fim_money_returns ret"
                    + " Left Join fim_money_requests AS mr ON ret.money_req_seq = mr.money_req_seq"
                    + " WHERE ret.record_status = 'A'"
                    + " AND ret.tran_grp_seq is null"
                    + " AND coalesce(mr.use_advance, 0) <> 1"
                    + (!string.IsNullOrEmpty(bgm_source_seq) ? " AND mr.bgm_source_seq = @bgm_source_seq" : "")
                    //+ (!string.IsNullOrEmpty(work_area_seq) ? " AND mr.work_area_seq = @work_area_seq" : "")
                #endregion
                #region icm_delivery_note
                    // delivery_note =========================================================================
                    //+ " union all "

                    //+ " SELECT distinct 'icm_delivery_note' as ref_type"
                    //+ ", 'G' as tran_type"
                    //+ ", dn.Code as ref_no"
                    //+ ", dn.ReceiveDate as tran_date"
                    //+ ", dn.Name as tran_name"
                    //+ ", 1 as work_area_seq"
                    //+ ", ppri.BgSourceId as bgm_source_seq"
                    //+ ", dn.DnId as ref_seq"
                    //+ ", ppr.BgYear as bgm_year"
                    //+ ", null as year_no, null as month_no"
                    //+ " FROM IcmDeliveryNote dn"
                    //+ " left join IcmPurchaseOrderInstallmentItem poii on dn.PoInstallId = poii.PoInstallId"
                    //+ " left join IcmBidderQuotation quo on poii.BidderQuoId = quo.BidderQuoId"
                    //+ " left join IcmPrePurchaseRequestItem ppri on quo.PrItemId = ppri.PrItemId"
                    //+ " left join IcmPrePurchaseRequest ppr on ppri.PprId = ppr.PprId"
                    //+ " WHERE dn.RecordStatus = 'A'"
                    //+ " AND dn.TranGrpId is null"
                    //+ (!string.IsNullOrEmpty(bgm_source_seq) ? " AND ppri.BgSourceId = @bgm_source_seq" : "")
                #endregion
                #region fim_transfers
                    // transfer =========================================================================
                    + " union all "

                    + " SELECT 'fim_transfers' as ref_type"
                    + ", 'G' as tran_type"
                    + ", t.ref_no"
                    + ", t.transfer_date as tran_date"
                    + ", concat(' ', coalesce(t.transfer_name, '')) as tran_name"
                    + ", 1 as work_area_seq"
                    + ", t.bgm_source_seq as bgm_source_seq"
                    + ", t.transfer_seq as ref_seq"
                    + ", t.bgm_year"
                    + ", null as year_no, null as month_no"

                    + " FROM fim_transfers t"
                    + " WHERE t.record_status not like 'D'"
                    + " AND t.tran_grp_seq is null"
                    + (!string.IsNullOrEmpty(bgm_source_seq) ? " AND t.bgm_source_seq = @bgm_source_seq" : "")
                #endregion
                #region fim_cheques
 + " union all "

                    + " SELECT distinct 'fim_cheques' as ref_type"
                    + ", 'G' as tran_type"
                    + ", concat('CH', c.cheque_no) as ref_no"
                    + ", c.actual_pay_date as tran_date"
                    + ", concat('auto ', c.cheque_no) as tran_name"
                    + ", 1 as work_area_seq"
                    + ", bacc.bgm_source_seq as bgm_source_seq"
                    + ", c.cheque_seq as ref_seq"
                    + ", p.bgm_year"
                    + ", null as year_no, null as month_no"

                    + " FROM fim_cheques c"
                    + " inner join fim_payment_cheques pc on c.cheque_seq = pc.cheque_seq"
                        + " and pc.record_status not like 'D'"
                    + " inner join fim_payments p on pc.pay_seq = p.pay_seq"
                        + " and p.record_status = 'A'"
                    + " inner join fim_mas_bank_accounts bacc on c.bank_acc_seq = bacc.bank_acc_seq"
                    + " inner join fim_mas_bank_accounts bacc1 on bacc.saving_bank_acc_seq = bacc1.bank_acc_seq"//ต้องผูกบัญชีออมทรัพย์แล้ว
                    + " WHERE c.record_status not like 'D'"
                    + " and c.cheque_status = 3"
                    + " AND c.tran_grp_seq is null"
                    + " AND c.actual_pay_date is not null"
                    + (!string.IsNullOrEmpty(bgm_source_seq) ? " AND bacc.bgm_source_seq = @bgm_source_seq" : "")
                #endregion
                #region fim_vat
 + @"
 union all

SELECT 'fim_vat' as ref_type
, 'G' as tran_type
, concat('V-', v.year_no, '-', v.month_no) as ref_no
, case when v.pay_seq is null
    then null
    else p.pay_date
    end as tran_date
, concat('คำนวนภาษี vat V-', v.year_no, '-', v.month_no) as tran_name
, 1 as work_area_seq
, v.bgm_source_seq as bgm_source_seq
, v.vat_seq as ref_seq
, v.bgm_year
, v.year_no, v.month_no
from fim_vat v
left join fim_payments p on v.pay_seq = p.pay_seq
where v.record_status <> 'D'
and v.tran_grp_seq is null"
                    + (!string.IsNullOrEmpty(bgm_source_seq) ? " AND v.bgm_source_seq = @bgm_source_seq" : "")
                #endregion
 + ") t"
                    + " where 0 = 0"
                    + (!string.IsNullOrEmpty(ref_type) ? " and t.ref_type = '" + ref_type + "'" : "")
                    + " order by tran_date";
                //, dateadd(day, -1,dateadd(month, 1, concat(v.year_no - 543, '-', v.month_no, '-', 1))) as tran_date

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                if (drc.Rows.Count > 0)
                {
                    for (int i = 0; i < drc.Rows.Count; i++)
                    {
                        M_ACM_GENERATE_ACCOUNT_Auto info = new M_ACM_GENERATE_ACCOUNT_Auto();
                        info.ref_type = drc.Rows[i]["ref_type"].ToString();
                        info.tran_type = drc.Rows[i]["tran_type"].ToString();
                        info.ref_no = drc.Rows[i]["ref_no"].ToString();
                        info.tran_date = cn.ConvertNullDateTime(drc.Rows[i]["tran_date"]) ?? new DateTime((cn.ConvertNullInt(drc.Rows[i]["year_no"]) ?? 0) - 543, cn.ConvertNullInt(drc.Rows[i]["month_no"]) ?? 0, 1).AddMonths(1).AddDays(-1);//cn.ConvertNullDateTime(drc.Rows[i]["tran_date"]);
                        info.tran_name = drc.Rows[i]["tran_name"].ToString();
                        info.work_area_seq = cn.ConvertNullInt(drc.Rows[i]["work_area_seq"]);
                        info.bgm_source_seq = cn.ConvertNullInt(drc.Rows[i]["bgm_source_seq"]);
                        info.ref_seq = cn.ConvertNullInt(drc.Rows[i]["ref_seq"]);
                        info.bgm_year = cn.ConvertNullInt(drc.Rows[i]["bgm_year"]);
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }
            return infoList;
        }

        public IList<M_ACM_GENERATE_ACCOUNT_Auto> GetListDetail(Nullable<Int32> ref_seq, string ref_type)
        {
            DataTable dtb = null;
            string strSQL = "";
            dbManager.CreateParameters(1);
            dbManager.AddParameters(0, "@ref_seq", ref_seq);
            IList<M_ACM_GENERATE_ACCOUNT_Auto> infoList = new List<M_ACM_GENERATE_ACCOUNT_Auto>();
            try
            {
                dbManager.Open();

                string strEventSeq = "";

                switch (ref_type)
                {
                    #region fim_petitions
                    case "fim_petitions":
                        strSQL = "SELECT ba.wait_income_bb_account_seq as account_seq"
                            + ", acc.account_code"
                            + ", acc.account_name"
                            + ", 'Dr' as dr_or_cr"
                            + ", ptd.amount as amount_dr"
                            + ", null as amount_cr"
                            + " FROM fim_petitions pt"
                            + " LEFT JOIN (select petition_seq"
                                + " , sum(coalesce(amount_real, 0)) as amount"
                                + " from fim_petition_dtls"
                                + " where record_status not like 'D'"
                                + " group by petition_seq"
                                + " ) ptd ON pt.petition_seq = ptd.petition_seq"
                            + " cross join acm_mas_base_account ba"
                            + " LEFT JOIN acm_mas_account acc ON ba.wait_income_bb_account_seq = acc.account_seq"
                            + " WHERE pt.petition_seq = @ref_seq"

                            + " union all"

                            + " SELECT ba.income_bb_account_seq as account_seq"
                            + ", acc.account_code"
                            + ", acc.account_name"
                            + ", 'Cr' as dr_or_cr"
                            + ", null as amount_dr"
                            + ", ptd.amount as amount_cr"
                            + " FROM fim_petitions pt"
                            + " LEFT JOIN (select petition_seq"
                                + " , sum(coalesce(amount_real, 0)) as amount"
                                + " from fim_petition_dtls"
                                + " where record_status not like 'D'"
                                + " group by petition_seq"
                                + " ) ptd ON pt.petition_seq = ptd.petition_seq"
                            + " cross join acm_mas_base_account ba"
                            + " LEFT JOIN acm_mas_account acc ON ba.income_bb_account_seq = acc.account_seq"
                            + " WHERE pt.petition_seq = @ref_seq";
                        break;
                    #endregion
                    #region fim_money_requests
                    case "fim_money_requests":
                        strSQL = "SELECT event_seq"
                            + " FROM fim_money_requests"
                            + " WHERE money_req_seq = @ref_seq";
                        dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];

                        if (dtb.Rows.Count > 0)
                        {
                            strEventSeq = dtb.Rows[0]["event_seq"].ToString();
                        }

                        switch (strEventSeq)
                        {
                            case "11":
                                strSQL = "SELECT ba.direct_pay_account_seq as account_seq"
                                    + ", acc.account_code, acc.account_name"
                                    + ", 'Dr' as dr_or_cr"
                                    + ", coalesce(mrd.amount, 0) as amount_dr"
                                    + ", null as amount_cr"
                                    + " FROM fim_money_requests mr"
                                    + " LEFT JOIN (select mrd.money_req_seq"
                                        + ", sum(coalesce(mrd.amount, 0)) as amount"
                                        + ", sum(coalesce(mrd.amount_vat, 0)) as amount_vat"
                                        + " from fim_money_request_dtls mrd"
                                        + " where mrd.record_status not like 'D'"
                                        + " group by mrd.money_req_seq"
                                        + ") mrd ON mr.money_req_seq = mrd.money_req_seq"
                                    + " cross join acm_mas_base_account ba"
                                    + " LEFT JOIN acm_mas_account acc ON ba.direct_pay_account_seq = acc.account_seq"
                                    + " WHERE mr.money_req_seq = @ref_seq"

                                    + " UNION ALL"

                                    + " SELECT ba.dn_cr_account_seq as account_seq"
                                    + ", acc.account_code, acc.account_name"
                                    + ", 'Cr' as dr_or_cr"
                                    + ", null as amount_dr"
                                    + ", mrd.amount as amount_cr"
                                    + " FROM fim_money_requests mr"
                                    + " LEFT JOIN (select mrd.money_req_seq"
                                        + ", sum(coalesce(mrd.amount, 0)) as amount"
                                        + " from fim_money_request_dtls mrd"
                                        + " where mrd.record_status not like 'D'"
                                        + " group by mrd.money_req_seq"
                                        + ") mrd ON mr.money_req_seq = mrd.money_req_seq"
                                    + " cross join acm_mas_base_account ba"
                                    + " LEFT JOIN acm_mas_account acc ON ba.dn_cr_account_seq = acc.account_seq"
                                    + " WHERE mr.money_req_seq = @ref_seq";
                                break;
                            case "28":
                                strSQL = "SELECT exp.account_seq as account_seq"
                                    + ", acc.account_code, acc.account_name"
                                    + ", 'Dr' as dr_or_cr"
                                    + ", coalesce(mrd.amount, 0) - coalesce(mrd.amount_vat, 0) as amount_dr"
                                    + ", null as amount_cr"
                                    + " FROM fim_money_requests mr"
                                    + " LEFT JOIN (select mrd.money_req_seq, mrd.bgm_proj_oper_exp_seq"
                                        + ", sum(coalesce(mrd.amount, 0)) as amount"
                                        + ", sum(coalesce(mrd.amount_vat, 0)) as amount_vat"
                                        + " from fim_money_request_dtls mrd"
                                        + " where mrd.record_status not like 'D'"
                                        + " group by mrd.money_req_seq, mrd.bgm_proj_oper_exp_seq"
                                        + ") mrd ON mr.money_req_seq = mrd.money_req_seq"
                                    + " LEFT JOIN bgm_project_operation_expense pjexp ON mrd.bgm_proj_oper_exp_seq = pjexp.bgm_proj_oper_exp_seq"
                                    + " LEFT JOIN bgm_project_expense recexp ON pjexp.bgm_proj_exp_seq = recexp.bgm_proj_exp_seq"
                                    + " LEFT JOIN bgm_mas_expense exp ON recexp.bgm_exp_seq = exp.bgm_exp_seq"
                                    + " LEFT JOIN acm_mas_account acc ON exp.account_seq = acc.account_seq"
                                    + " WHERE mr.money_req_seq = @ref_seq"

                                    + " UNION ALL"

                                    + " SELECT ba.payroll_tax_account_seq as account_seq"
                                    + ", acc.account_code, acc.account_name"
                                    + ", 'Cr' as dr_or_cr"
                                    + ", null as amount_dr"
                                    + ", mrd.amount as amount_cr"
                                    + " FROM fim_money_requests mr"
                                    + " LEFT JOIN (select mrd.money_req_seq"
                                        + ", sum(coalesce(mrd.amount, 0)) as amount"
                                        + " from fim_money_request_dtls mrd"
                                        + " where mrd.record_status not like 'D'"
                                        + " group by mrd.money_req_seq"
                                        + ") mrd ON mr.money_req_seq = mrd.money_req_seq"
                                    + " cross join acm_mas_base_account ba"
                                    + " LEFT JOIN acm_mas_account acc ON ba.payroll_tax_account_seq = acc.account_seq"
                                    + " WHERE mr.money_req_seq = @ref_seq";
                                break;
                            case "12":
                            case "14":
                            case "15":
                            case "17":
                            case "18":
                            default:
                                strSQL = "SELECT exp.account_seq as account_seq"
                                    + ", acc.account_code, acc.account_name"
                                    + ", 'Dr' as dr_or_cr"
                                    + ", coalesce(mrd.amount, 0) - coalesce(mrd.amount_vat, 0) as amount_dr"
                                    + ", null as amount_cr"
                                    + " FROM fim_money_requests mr"
                                    + " LEFT JOIN (select mrd.money_req_seq, mrd.bgm_proj_oper_exp_seq"
                                        + ", sum(coalesce(mrd.amount, 0)) as amount"
                                        + ", sum(coalesce(mrd.amount_vat, 0)) as amount_vat"
                                        + " from fim_money_request_dtls mrd"
                                        + " where mrd.record_status not like 'D'"
                                        + " group by mrd.money_req_seq, mrd.bgm_proj_oper_exp_seq"
                                        + ") mrd ON mr.money_req_seq = mrd.money_req_seq"
                                    + " LEFT JOIN bgm_project_operation_expense pjexp ON mrd.bgm_proj_oper_exp_seq = pjexp.bgm_proj_oper_exp_seq"
                                    + " LEFT JOIN bgm_project_expense recexp ON pjexp.bgm_proj_exp_seq = recexp.bgm_proj_exp_seq"
                                    + " LEFT JOIN bgm_mas_expense exp ON recexp.bgm_exp_seq = exp.bgm_exp_seq"
                                    + " LEFT JOIN acm_mas_account acc ON exp.account_seq = acc.account_seq"
                                    + " WHERE mr.money_req_seq = @ref_seq"

                                    + " UNION ALL"

                                    + " SELECT ba.suspense_input_tax_account_seq as account_seq"
                                    + ", acc2.account_code as account_code"
                                    + ", acc2.account_name as account_name"
                                    + ", 'Dr' as dr_or_cr"
                                    + ", coalesce(mrd.amount_vat, 0) as amount_dr"
                                    + ", null as amount_cr"
                                    + " FROM fim_money_requests mr"
                                    + " LEFT JOIN (select mrd.money_req_seq"
                                        + ", sum(coalesce(mrd.amount_vat, 0)) as amount_vat"
                                        + " from fim_money_request_dtls mrd"
                                        + " where mrd.record_status not like 'D'"
                                        + " group by mrd.money_req_seq"
                                        + ") mrd ON mr.money_req_seq = mrd.money_req_seq"
                                    + " cross join acm_mas_base_account ba"
                                    + " LEFT JOIN acm_mas_account acc2 ON ba.suspense_input_tax_account_seq = acc2.account_seq"
                                    + " WHERE mr.money_req_seq = @ref_seq"

                                    + " UNION ALL"

                                    + " SELECT ba.remain_account_seq as account_seq"
                                    + ", acc.account_code, acc.account_name"
                                    + ", 'Cr' as dr_or_cr"
                                    + ", null as amount_dr"
                                    + ", mrd.amount as amount_cr"
                                    + " FROM fim_money_requests mr"
                                    + " LEFT JOIN (select mrd.money_req_seq"
                                        + ", sum(coalesce(mrd.amount, 0)) as amount"
                                        + " from fim_money_request_dtls mrd"
                                        + " where mrd.record_status not like 'D'"
                                        + " group by mrd.money_req_seq"
                                        + ") mrd ON mr.money_req_seq = mrd.money_req_seq"
                                    + " cross join acm_mas_base_account ba"
                                    + " LEFT JOIN acm_mas_account acc ON ba.remain_account_seq = acc.account_seq"
                                    + " WHERE mr.money_req_seq = @ref_seq";
                                break;
                        }
                        break;
                    #endregion
                    #region fim_payments
                    case "fim_payments":
                        bool chkTaxInvoice = true;
                        strSQL = "SELECT p.event_seq, mr.chk_tax_invoice, dn.ChkTaxInvoice"
                            + " from fim_payments p"
                            + " left join fim_money_requests mr on p.money_req_seq = mr.money_req_seq"
                            + " left join IcmDeliveryNote dn on mr.icm_dn_seq = dn.DnId"
                            + " WHERE pay_seq = @ref_seq";
                        dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];

                        if (dtb.Rows.Count > 0)
                        {
                            strEventSeq = dtb.Rows[0]["event_seq"].ToString();
                            if (strEventSeq == "11")
                            {
                                chkTaxInvoice = Convert.ToBoolean(cn.ConvertNullInt(dtb.Rows[0]["ChkTaxInvoice"]) ?? 1);
                            }
                            else
                            {
                                chkTaxInvoice = Convert.ToBoolean(cn.ConvertNullInt(dtb.Rows[0]["chk_tax_invoice"]) ?? 1);
                            }
                        }

                        strSQL = "";

                        switch (strEventSeq)
                        {
                            case "11":
                                // ค่าใช้จ่าย
                                strSQL = "SELECT ba.dn_cr_account_seq as account_seq"
                                    + ", acc1.account_code as account_code"
                                    + ", acc1.account_name account_name"
                                    + ", 'Dr' as dr_or_cr"
                                    + ", coalesce(pay.amount, 0) + coalesce(pay.tax, 0) as amount_dr"
                                    + ", null as amount_cr"
                                    + " FROM fim_payments pay"
                                    + " cross join acm_mas_base_account ba"
                                    + " LEFT JOIN acm_mas_account acc1 ON ba.dn_cr_account_seq = acc1.account_seq"
                                    + " WHERE pay.pay_seq = @ref_seq"

                                    + " union all"
                                    // เครดิต เงินสด/เงินฝาก
                                    + " SELECT case pay.method_seq"
                                        + " when 1 then ba.cash_account_seq"
                                        + " when 2 then bacc.account_seq"
                                        + " when 3 then case coalesce(pet.direct_pay, '0')"
                                                        + " when '0' then bacc3.account_seq"
                                                        + " when '1' then ba.income_bb_account_seq"
                                                        + " end"
                                        + " when 4 then bacc2.account_seq"
                                        + " end as account_seq"
                                    + ", case pay.method_seq"
                                        + " when 1 then acc2.account_code"
                                        + " when 2 then acc1.account_code"
                                        + " when 3 then case coalesce(pet.direct_pay, '0')"
                                                        + " when '0' then acc5.account_code"
                                                        + " when '1' then acc3.account_code"
                                                        + " end"
                                        + " when 4 then acc4.account_code"
                                        + " end as account_code"
                                    + ", case pay.method_seq"
                                        + " when 1 then acc2.account_name"
                                        + " when 2 then acc1.account_name"
                                        + " when 3 then case coalesce(pet.direct_pay, '0')"
                                                        + " when '0' then acc5.account_name"
                                                        + " when '1' then acc3.account_name"
                                                        + " end"
                                        + " when 4 then acc4.account_name"
                                        + " end as account_name"
                                    + ", 'Cr' as dr_or_cr"
                                    + ", null as amount_dr"
                                    + ", pay.amount as amount_cr"
                                    + " FROM fim_payments pay"
                                    + " LEFT JOIN fim_payment_cheques pc on pay.pay_seq = pc.pay_seq"
                                        + " and pc.record_status not like 'D'"
                                    + " LEFT JOIN fim_payments_payinpack pip on pay.pay_seq = pip.pay_seq"
                                        + " and pip.record_status not like 'D'"
                                    + " LEFT JOIN fim_cheques che on pc.cheque_seq = che.cheque_seq"
                                    + " LEFT JOIN fim_mas_bank_accounts bacc on che.bank_acc_seq = bacc.bank_acc_seq"
                                    + " LEFT JOIN fim_mas_bank_accounts bacc2 on pip.bank_acc_seq = bacc2.bank_acc_seq"
                                    + " LEFT JOIN fim_mas_bank_accounts bacc3 on pay.from_bank_acc_id = bacc3.bank_acc_seq"
                                    + " LEFT JOIN fim_petitions pet on pay.petition_seq = pet.petition_seq"
                                    + " cross join acm_mas_base_account ba"
                                    + " LEFT JOIN acm_mas_account acc1 ON bacc.account_seq = acc1.account_seq"
                                    + " LEFT JOIN acm_mas_account acc2 ON ba.cash_account_seq = acc2.account_seq"
                                    + " LEFT JOIN acm_mas_account acc3 ON ba.income_bb_account_seq = acc3.account_seq"
                                    + " LEFT JOIN acm_mas_account acc4 ON bacc2.account_seq = acc4.account_seq"
                                    + " LEFT JOIN acm_mas_account acc5 ON bacc3.account_seq = acc5.account_seq"
                                    + " WHERE pay.pay_seq = @ref_seq"

                                    + " union all"

                                    + " SELECT case when c.ConGrpId is null or c.ConGrpId = 47 then ba.personal_tax_account_seq"
                                        + " else ba.company_tax_account_seq"
                                        + " end as account_seq"
                                    + ", case when c.ConGrpId is null or c.ConGrpId = 47 then acc1.account_code"
                                        + " else acc2.account_code"
                                        + " end as account_code"
                                    + ", case when c.ConGrpId is null or c.ConGrpId = 47 then acc1.account_name"
                                        + " else acc2.account_name"
                                        + " end as account_name"
                                    + ", 'Cr' as dr_or_cr"
                                    + ", null as amount_dr"
                                    + ", pay.tax as amount_cr"
                                    + " FROM fim_payments pay"
                                    + " cross join acm_mas_base_account ba"
                                    + " LEFT JOIN IcmMasContact c ON pay.contact_seq = c.ConId"
                                    + " LEFT JOIN acm_mas_account acc1 ON ba.personal_tax_account_seq = acc1.account_seq"
                                    + " LEFT JOIN acm_mas_account acc2 ON ba.company_tax_account_seq = acc2.account_seq"
                                    + " WHERE pay.pay_seq = @ref_seq"
                                    ;
                                break;
                            case "21":
                                strSQL = "SELECT acc.account_seq"
                                    + ", acc.account_code"
                                    + ", acc.account_name"
                                    + ", 'Dr' as dr_or_cr"
                                    + ", coalesce(tax.amount, 0) as amount_dr"
                                    + ", null as amount_cr"
                                    + " FROM fim_payments pay"
                                    + " left join (select pay.tax_pay_seq"
                                        + ", sum(pay.tax) as amount"
                                        + " from fim_payments pay"
                                        + " left join IcmMasContact c on pay.contact_seq = c.ConId"
                                        + " where c.ConGrpId = 47"
                                        + " group by pay.tax_pay_seq"
                                        + ") tax on pay.pay_seq = tax.tax_pay_seq"
                                    + " cross join acm_mas_base_account ba"
                                    + " LEFT JOIN acm_mas_account acc ON ba.personal_tax_account_seq = acc.account_seq"
                                    + " WHERE pay.pay_seq = @ref_seq"

                                    + " union all"

                                    + " SELECT acc.account_seq"
                                    + ", acc.account_code"
                                    + ", acc.account_name"
                                    + ", 'Dr' as dr_or_cr"
                                    + ", coalesce(tax.amount, 0) as amount_dr"
                                    + ", null as amount_cr"
                                    + " FROM fim_payments pay"
                                    + " left join (select pay.tax_pay_seq"
                                        + ", sum(pay.tax) as amount"
                                        + " from fim_payments pay"
                                        + " left join IcmMasContact c on pay.contact_seq = c.ConId"
                                        + " where c.ConGrpId <> 47"
                                        + " group by pay.tax_pay_seq"
                                        + ") tax on pay.pay_seq = tax.tax_pay_seq"
                                    + " cross join acm_mas_base_account ba"
                                    + " LEFT JOIN acm_mas_account acc ON ba.company_tax_account_seq = acc.account_seq"
                                    + " WHERE pay.pay_seq = @ref_seq"

                                    + " union all"
                                    // เครดิต เงินสด/เงินฝาก
                                    + " SELECT case pay.method_seq"
                                        + " when 1 then ba.cash_account_seq"
                                        + " when 2 then bacc.account_seq"
                                        + " when 3 then case coalesce(pet.direct_pay, '0')"
                                                        + " when '0' then bacc3.account_seq"
                                                        + " when '1' then ba.income_bb_account_seq"
                                                        + " end"
                                        + " when 4 then bacc2.account_seq"
                                        + " end as account_seq"
                                    + ", case pay.method_seq"
                                        + " when 1 then acc2.account_code"
                                        + " when 2 then acc1.account_code"
                                        + " when 3 then case coalesce(pet.direct_pay, '0')"
                                                        + " when '0' then acc5.account_code"
                                                        + " when '1' then acc3.account_code"
                                                        + " end"
                                        + " when 4 then acc4.account_code"
                                        + " end as account_code"
                                    + ", case pay.method_seq"
                                        + " when 1 then acc2.account_name"
                                        + " when 2 then acc1.account_name"
                                        + " when 3 then case coalesce(pet.direct_pay, '0')"
                                                        + " when '0' then acc5.account_name"
                                                        + " when '1' then acc3.account_name"
                                                        + " end"
                                        + " when 4 then acc4.account_name"
                                        + " end as account_name"
                                    + ", 'Cr' as dr_or_cr"
                                    + ", null as amount_dr"
                                    + ", pay.amount as amount_cr"
                                    + " FROM fim_payments pay"
                                    + " LEFT JOIN fim_payment_cheques pc on pay.pay_seq = pc.pay_seq"
                                        + " and pc.record_status not like 'D'"
                                    + " LEFT JOIN fim_payments_payinpack pip on pay.pay_seq = pip.pay_seq"
                                        + " and pip.record_status not like 'D'"
                                    + " LEFT JOIN fim_cheques che on pc.cheque_seq = che.cheque_seq"
                                    + " LEFT JOIN fim_mas_bank_accounts bacc on che.bank_acc_seq = bacc.bank_acc_seq"
                                    + " LEFT JOIN fim_mas_bank_accounts bacc2 on pip.bank_acc_seq = bacc2.bank_acc_seq"
                                    + " LEFT JOIN fim_mas_bank_accounts bacc3 on pay.from_bank_acc_id = bacc3.bank_acc_seq"
                                    + " LEFT JOIN fim_petitions pet on pay.petition_seq = pet.petition_seq"
                                    + " cross join acm_mas_base_account ba"
                                    + " LEFT JOIN acm_mas_account acc1 ON bacc.account_seq = acc1.account_seq"
                                    + " LEFT JOIN acm_mas_account acc2 ON ba.cash_account_seq = acc2.account_seq"
                                    + " LEFT JOIN acm_mas_account acc3 ON ba.income_bb_account_seq = acc3.account_seq"
                                    + " LEFT JOIN acm_mas_account acc4 ON bacc2.account_seq = acc4.account_seq"
                                    + " LEFT JOIN acm_mas_account acc5 ON bacc3.account_seq = acc5.account_seq"
                                    + " WHERE pay.pay_seq = @ref_seq"
                                    ;
                                break;
                            case "26":
                                strSQL = "select ba.revenue_vat_creditor_account_seq as account_seq"
                                    + ", acc.account_code as account_code"
                                    + ", acc.account_name as account_name"
                                    + ", 'Dr' as dr_or_cr"
                                    + ", coalesce(pay.amount, 0) as amount_dr"
                                    + ", null as amount_cr"
                                    + " from fim_payments pay"
                                    + " cross join acm_mas_base_account ba"
                                    + " left join acm_mas_account acc on ba.revenue_vat_creditor_account_seq = acc.account_seq"
                                    + " WHERE pay.pay_seq = @ref_seq"

                                    + " union all"
                                    // เครดิต เงินสด/เงินฝาก
                                    + " SELECT case pay.method_seq"
                                        + " when 1 then ba.cash_account_seq"
                                        + " when 2 then bacc.account_seq"
                                        + " when 3 then case coalesce(pet.direct_pay, '0')"
                                                        + " when '0' then bacc3.account_seq"
                                                        + " when '1' then ba.income_bb_account_seq"
                                                        + " end"
                                        + " when 4 then bacc2.account_seq"
                                        + " end as account_seq"
                                    + ", case pay.method_seq"
                                        + " when 1 then acc2.account_code"
                                        + " when 2 then acc1.account_code"
                                        + " when 3 then case coalesce(pet.direct_pay, '0')"
                                                        + " when '0' then acc5.account_code"
                                                        + " when '1' then acc3.account_code"
                                                        + " end"
                                        + " when 4 then acc4.account_code"
                                        + " end as account_code"
                                    + ", case pay.method_seq"
                                        + " when 1 then acc2.account_name"
                                        + " when 2 then acc1.account_name"
                                        + " when 3 then case coalesce(pet.direct_pay, '0')"
                                                        + " when '0' then acc5.account_name"
                                                        + " when '1' then acc3.account_name"
                                                        + " end"
                                        + " when 4 then acc4.account_name"
                                        + " end as account_name"
                                    + ", 'Cr' as dr_or_cr"
                                    + ", null as amount_dr"
                                    + ", pay.amount as amount_cr"
                                    + " FROM fim_payments pay"
                                    + " LEFT JOIN fim_payment_cheques pc on pay.pay_seq = pc.pay_seq"
                                        + " and pc.record_status not like 'D'"
                                    + " LEFT JOIN fim_payments_payinpack pip on pay.pay_seq = pip.pay_seq"
                                        + " and pip.record_status not like 'D'"
                                    + " LEFT JOIN fim_cheques che on pc.cheque_seq = che.cheque_seq"
                                    + " LEFT JOIN fim_mas_bank_accounts bacc on che.bank_acc_seq = bacc.bank_acc_seq"
                                    + " LEFT JOIN fim_mas_bank_accounts bacc2 on pip.bank_acc_seq = bacc2.bank_acc_seq"
                                    + " LEFT JOIN fim_mas_bank_accounts bacc3 on pay.from_bank_acc_id = bacc3.bank_acc_seq"
                                    + " LEFT JOIN fim_petitions pet on pay.petition_seq = pet.petition_seq"
                                    + " cross join acm_mas_base_account ba"
                                    + " LEFT JOIN acm_mas_account acc1 ON bacc.account_seq = acc1.account_seq"
                                    + " LEFT JOIN acm_mas_account acc2 ON ba.cash_account_seq = acc2.account_seq"
                                    + " LEFT JOIN acm_mas_account acc3 ON ba.income_bb_account_seq = acc3.account_seq"
                                    + " LEFT JOIN acm_mas_account acc4 ON bacc2.account_seq = acc4.account_seq"
                                    + " LEFT JOIN acm_mas_account acc5 ON bacc3.account_seq = acc5.account_seq"
                                    + " WHERE pay.pay_seq = @ref_seq"
                                    ;
                                break;
                            case "27":
                                strSQL = "SELECT distinct rt.account_seq"
                                    + ", acc1.account_code"
                                    + ", acc1.account_name"
                                    + ", 'Dr' as dr_or_cr"
                                    + ", coalesce(pay.amount, 0) - coalesce(pay.vat, 0) as amount_dr"
                                    + ", null as amount_cr"
                                    + " FROM fim_payments pay"
                                    + " inner join fim_receives r on pay.pay_seq = r.pay_seq"
                                    + " inner join fim_mas_receive_types rt on r.receive_type_seq = rt.receive_type_seq"
                                    + " LEFT JOIN acm_mas_account acc1 ON rt.account_seq = acc1.account_seq"
                                    + " WHERE pay.pay_seq = @ref_seq"

                                    + " union all"

                                    + " SELECT ba.sale_vat_account_seq as account_seq"
                                    + ", acc1.account_code as account_code"
                                    + ", acc1.account_name as account_code"
                                    + ", 'Dr' as dr_or_cr"
                                    + ", coalesce(pay.vat, 0) as amount_dr"
                                    + ", null as amount_cr"
                                    + " FROM fim_payments pay"
                                    + " cross join acm_mas_base_account ba"
                                    + " LEFT JOIN acm_mas_account acc1 ON ba.sale_vat_account_seq = acc1.account_seq"
                                    + " WHERE pay.pay_seq = @ref_seq"
                                    + " and coalesce(pay.vat, 0) > 0"

                                    + " union all"
                                    // เครดิต เงินสด/เงินฝาก
                                    + " SELECT case pay.method_seq"
                                        + " when 1 then ba.cash_account_seq"
                                        + " when 2 then bacc.account_seq"
                                        + " when 3 then case coalesce(pet.direct_pay, '0')"
                                                        + " when '0' then bacc3.account_seq"
                                                        + " when '1' then ba.income_bb_account_seq"
                                                        + " end"
                                        + " when 4 then bacc2.account_seq"
                                        + " end as account_seq"
                                    + ", case pay.method_seq"
                                        + " when 1 then acc2.account_code"
                                        + " when 2 then acc1.account_code"
                                        + " when 3 then case coalesce(pet.direct_pay, '0')"
                                                        + " when '0' then acc5.account_code"
                                                        + " when '1' then acc3.account_code"
                                                        + " end"
                                        + " when 4 then acc4.account_code"
                                        + " end as account_code"
                                    + ", case pay.method_seq"
                                        + " when 1 then acc2.account_name"
                                        + " when 2 then acc1.account_name"
                                        + " when 3 then case coalesce(pet.direct_pay, '0')"
                                                        + " when '0' then acc5.account_name"
                                                        + " when '1' then acc3.account_name"
                                                        + " end"
                                        + " when 4 then acc4.account_name"
                                        + " end as account_name"
                                    + ", 'Cr' as dr_or_cr"
                                    + ", null as amount_dr"
                                    + ", pay.amount as amount_cr"
                                    + " FROM fim_payments pay"
                                    + " LEFT JOIN fim_payment_cheques pc on pay.pay_seq = pc.pay_seq"
                                        + " and pc.record_status not like 'D'"
                                    + " LEFT JOIN fim_payments_payinpack pip on pay.pay_seq = pip.pay_seq"
                                        + " and pip.record_status not like 'D'"
                                    + " LEFT JOIN fim_cheques che on pc.cheque_seq = che.cheque_seq"
                                    + " LEFT JOIN fim_mas_bank_accounts bacc on che.bank_acc_seq = bacc.bank_acc_seq"
                                    + " LEFT JOIN fim_mas_bank_accounts bacc2 on pip.bank_acc_seq = bacc2.bank_acc_seq"
                                    + " LEFT JOIN fim_mas_bank_accounts bacc3 on pay.from_bank_acc_id = bacc3.bank_acc_seq"
                                    + " LEFT JOIN fim_petitions pet on pay.petition_seq = pet.petition_seq"
                                    + " cross join acm_mas_base_account ba"
                                    + " LEFT JOIN acm_mas_account acc1 ON bacc.account_seq = acc1.account_seq"
                                    + " LEFT JOIN acm_mas_account acc2 ON ba.cash_account_seq = acc2.account_seq"
                                    + " LEFT JOIN acm_mas_account acc3 ON ba.income_bb_account_seq = acc3.account_seq"
                                    + " LEFT JOIN acm_mas_account acc4 ON bacc2.account_seq = acc4.account_seq"
                                    + " LEFT JOIN acm_mas_account acc5 ON bacc3.account_seq = acc5.account_seq"
                                    + " WHERE pay.pay_seq = @ref_seq"
                                    ;
                                break;
                            case "28":
                                strSQL = "select ba.payroll_tax_account_seq as account_seq"
                                    + ", acc.account_code as account_code"
                                    + ", acc.account_name as account_name"
                                    + ", 'Dr' as dr_or_cr"
                                    + ", coalesce(pay.amount, 0) as amount_dr"
                                    + ", null as amount_cr"
                                    + " from fim_payments pay"
                                    + " cross join acm_mas_base_account ba"
                                    + " left join acm_mas_account acc on ba.payroll_tax_account_seq = acc.account_seq"
                                    + " WHERE pay.pay_seq = @ref_seq"

                                    + " union all"
                                    // เครดิต เงินสด/เงินฝาก
                                    + " SELECT case pay.method_seq"
                                        + " when 1 then ba.cash_account_seq"
                                        + " when 2 then bacc.account_seq"
                                        + " when 3 then case coalesce(pet.direct_pay, '0')"
                                                        + " when '0' then bacc3.account_seq"
                                                        + " when '1' then ba.income_bb_account_seq"
                                                        + " end"
                                        + " when 4 then bacc2.account_seq"
                                        + " end as account_seq"
                                    + ", case pay.method_seq"
                                        + " when 1 then acc2.account_code"
                                        + " when 2 then acc1.account_code"
                                        + " when 3 then case coalesce(pet.direct_pay, '0')"
                                                        + " when '0' then acc5.account_code"
                                                        + " when '1' then acc3.account_code"
                                                        + " end"
                                        + " when 4 then acc4.account_code"
                                        + " end as account_code"
                                    + ", case pay.method_seq"
                                        + " when 1 then acc2.account_name"
                                        + " when 2 then acc1.account_name"
                                        + " when 3 then case coalesce(pet.direct_pay, '0')"
                                                        + " when '0' then acc5.account_name"
                                                        + " when '1' then acc3.account_name"
                                                        + " end"
                                        + " when 4 then acc4.account_name"
                                        + " end as account_name"
                                    + ", 'Cr' as dr_or_cr"
                                    + ", null as amount_dr"
                                    + ", pay.amount as amount_cr"
                                    + " FROM fim_payments pay"
                                    + " LEFT JOIN fim_payment_cheques pc on pay.pay_seq = pc.pay_seq"
                                        + " and pc.record_status not like 'D'"
                                    + " LEFT JOIN fim_payments_payinpack pip on pay.pay_seq = pip.pay_seq"
                                        + " and pip.record_status not like 'D'"
                                    + " LEFT JOIN fim_cheques che on pc.cheque_seq = che.cheque_seq"
                                    + " LEFT JOIN fim_mas_bank_accounts bacc on che.bank_acc_seq = bacc.bank_acc_seq"
                                    + " LEFT JOIN fim_mas_bank_accounts bacc2 on pip.bank_acc_seq = bacc2.bank_acc_seq"
                                    + " LEFT JOIN fim_mas_bank_accounts bacc3 on pay.from_bank_acc_id = bacc3.bank_acc_seq"
                                    + " LEFT JOIN fim_petitions pet on pay.petition_seq = pet.petition_seq"
                                    + " cross join acm_mas_base_account ba"
                                    + " LEFT JOIN acm_mas_account acc1 ON bacc.account_seq = acc1.account_seq"
                                    + " LEFT JOIN acm_mas_account acc2 ON ba.cash_account_seq = acc2.account_seq"
                                    + " LEFT JOIN acm_mas_account acc3 ON ba.income_bb_account_seq = acc3.account_seq"
                                    + " LEFT JOIN acm_mas_account acc4 ON bacc2.account_seq = acc4.account_seq"
                                    + " LEFT JOIN acm_mas_account acc5 ON bacc3.account_seq = acc5.account_seq"
                                    + " WHERE pay.pay_seq = @ref_seq"
                                    ;
                                break;
                            default:
                                // ค่าใช้จ่าย
                                strSQL = "SELECT case pay.event_seq when 16 then ba.loan_account_seq"
                                        + " else ba.remain_account_seq"
                                        + " end as account_seq"

                                    + ", case pay.event_seq when 16 then acc1.account_code"
                                        + " else acc2.account_code"
                                        + " end as account_code"

                                    + ", case pay.event_seq when 16 then acc1.account_name"
                                        + " else acc2.account_name"
                                        + " end as account_name"

                                    + ", 'Dr' as dr_or_cr"
                                    + ", coalesce(pay.amount, 0) + coalesce(pay.tax, 0) as amount_dr"
                                    + ", null as amount_cr"
                                    + " FROM fim_payments pay"
                                    + " cross join acm_mas_base_account ba"
                                    + " LEFT JOIN acm_mas_account acc1 ON ba.loan_account_seq = acc1.account_seq"
                                    + " LEFT JOIN acm_mas_account acc2 ON ba.remain_account_seq = acc2.account_seq"
                                    + " WHERE pay.pay_seq = @ref_seq"

                                    + " union all"
                                    // เครดิต เงินสด/เงินฝาก
                                    + " SELECT case pay.method_seq"
                                        + " when 1 then ba.cash_account_seq"
                                        + " when 2 then bacc.account_seq"
                                        + " when 3 then case coalesce(pet.direct_pay, '0')"
                                                        + " when '0' then bacc3.account_seq"
                                                        + " when '1' then ba.income_bb_account_seq"
                                                        + " end"
                                        + " when 4 then bacc2.account_seq"
                                        + " end as account_seq"
                                    + ", case pay.method_seq"
                                        + " when 1 then acc2.account_code"
                                        + " when 2 then acc1.account_code"
                                        + " when 3 then case coalesce(pet.direct_pay, '0')"
                                                        + " when '0' then acc5.account_code"
                                                        + " when '1' then acc3.account_code"
                                                        + " end"
                                        + " when 4 then acc4.account_code"
                                        + " end as account_code"
                                    + ", case pay.method_seq"
                                        + " when 1 then acc2.account_name"
                                        + " when 2 then acc1.account_name"
                                        + " when 3 then case coalesce(pet.direct_pay, '0')"
                                                        + " when '0' then acc5.account_name"
                                                        + " when '1' then acc3.account_name"
                                                        + " end"
                                        + " when 4 then acc4.account_name"
                                        + " end as account_name"
                                    + ", 'Cr' as dr_or_cr"
                                    + ", null as amount_dr"
                                    + ", pay.amount as amount_cr"
                                    + " FROM fim_payments pay"
                                    + " LEFT JOIN fim_payment_cheques pc on pay.pay_seq = pc.pay_seq"
                                        + " and pc.record_status not like 'D'"
                                    + " LEFT JOIN fim_payments_payinpack pip on pay.pay_seq = pip.pay_seq"
                                        + " and pip.record_status not like 'D'"
                                    + " LEFT JOIN fim_cheques che on pc.cheque_seq = che.cheque_seq"
                                    + " LEFT JOIN fim_mas_bank_accounts bacc on che.bank_acc_seq = bacc.bank_acc_seq"
                                    + " LEFT JOIN fim_mas_bank_accounts bacc2 on pip.bank_acc_seq = bacc2.bank_acc_seq"
                                    + " LEFT JOIN fim_mas_bank_accounts bacc3 on pay.from_bank_acc_id = bacc3.bank_acc_seq"
                                    + " LEFT JOIN fim_petitions pet on pay.petition_seq = pet.petition_seq"
                                    + " cross join acm_mas_base_account ba"
                                    + " LEFT JOIN acm_mas_account acc1 ON bacc.account_seq = acc1.account_seq"
                                    + " LEFT JOIN acm_mas_account acc2 ON ba.cash_account_seq = acc2.account_seq"
                                    + " LEFT JOIN acm_mas_account acc3 ON ba.income_bb_account_seq = acc3.account_seq"
                                    + " LEFT JOIN acm_mas_account acc4 ON bacc2.account_seq = acc4.account_seq"
                                    + " LEFT JOIN acm_mas_account acc5 ON bacc3.account_seq = acc5.account_seq"
                                    + " WHERE pay.pay_seq = @ref_seq"

                                    + " union all"
                                    // Tax
                                    + " SELECT case when c.ConGrpId is null or c.ConGrpId = 47 then ba.personal_tax_account_seq"
                                        + " else ba.company_tax_account_seq"
                                        + " end as account_seq"
                                    + ", case when c.ConGrpId is null or c.ConGrpId = 47 then acc1.account_code"
                                        + " else acc2.account_code"
                                        + " end as account_code"
                                    + ", case when c.ConGrpId is null or c.ConGrpId = 47 then acc1.account_name"
                                        + " else acc2.account_name"
                                        + " end as account_name"
                                    + ", 'Cr' as dr_or_cr"
                                    + ", null as amount_dr"
                                    + ", pay.tax as amount_cr"
                                    + " FROM fim_payments pay"
                                    + " cross join acm_mas_base_account ba"
                                    + " LEFT JOIN IcmMasContact c ON pay.contact_seq = c.ConId"
                                    + " LEFT JOIN acm_mas_account acc1 ON ba.personal_tax_account_seq = acc1.account_seq"
                                    + " LEFT JOIN acm_mas_account acc2 ON ba.company_tax_account_seq = acc2.account_seq"
                                    + " WHERE pay.pay_seq = @ref_seq"
                                    ;
                                break;
                        }

                        //if (!chkTaxInvoice)
                        //{
                        strSQL += (strEventSeq != "27" ? " union all"
                            + " SELECT ba.buy_vat_account_seq as account_seq"
                            + ", acc1.account_code as account_code"
                            + ", acc1.account_name as account_code"
                            + ", 'Dr' as dr_or_cr"
                            + ", coalesce(pay.vat, 0) as amount_dr"
                            + ", null as amount_cr"
                            + " FROM fim_payments pay"
                            + " cross join acm_mas_base_account ba"
                            + " LEFT JOIN acm_mas_account acc1 ON ba.buy_vat_account_seq = acc1.account_seq"
                            + " WHERE pay.pay_seq = @ref_seq"
                            + " and coalesce(pay.vat, 0) > 0"

                            + " union all"

                            + " SELECT ba.suspense_input_tax_account_seq as account_seq"
                            + ", acc1.account_code as account_code"
                            + ", acc1.account_name as account_code"
                            + ", 'Cr' as dr_or_cr"
                            + ", null as amount_dr"
                            + ", coalesce(pay.vat, 0) as amount_cr"
                            + " FROM fim_payments pay"
                            + " cross join acm_mas_base_account ba"
                            + " LEFT JOIN acm_mas_account acc1 ON ba.suspense_input_tax_account_seq = acc1.account_seq"
                            + " WHERE pay.pay_seq = @ref_seq"
                            + " and coalesce(pay.vat, 0) > 0" : "");
                        //}

                        break;
                    #endregion
                    #region fim_receives
                    case "fim_receives":
                        // bank or cash
                        strSQL = "SELECT case when r.method_seq = 1 or r.method_seq = 2"
                                + " then cv.cash_account_seq"
                                + " else ba.account_seq"
                                + " end as account_seq"
                            + ", case when r.method_seq = 1 or r.method_seq = 2"
                                + " then acc1.account_code"
                                + " else acc2.account_code"
                                + " end as account_code"
                            + ", case when r.method_seq = 1 or r.method_seq = 2"
                                + " then acc1.account_name"
                                + " else acc2.account_name"
                                + " end as account_name"
                            + ", 'Dr' as dr_or_cr"
                            + ", r.amount as amount_dr"
                            + ", null as amount_cr"
                            + " FROM fim_receives r"
                            + " LEFT JOIN fim_mas_bank_accounts ba ON r.bank_acc_seq = ba.bank_acc_seq"
                            + " cross join acm_mas_base_account cv"
                            + " LEFT JOIN acm_mas_account acc1 ON cv.cash_account_seq = acc1.account_seq"
                            + " LEFT JOIN acm_mas_account acc2 ON ba.account_seq = acc2.account_seq"
                            + " WHERE r.receive_seq = @ref_seq"
                            // tax
                            + " union all"

                            + " SELECT case c.ConGrpId"
                                + " when 47 then cv.personal_tax_account_seq"
                                + " else cv.company_tax_account_seq"
                                + " end as account_seq"
                            + ", case c.ConGrpId"
                                + " when 47 then acc1.account_code"
                                + " else acc2.account_code"
                                + " end as account_code"
                            + ", case c.ConGrpId"
                                + " when 47 then acc1.account_name"
                                + " else acc2.account_name"
                                + " end as account_name"
                            + ", 'Dr' as dr_or_cr"
                            + ", r.tax as amount_dr"
                            + ", null as amount_cr"
                            + " FROM fim_receives r"
                            + " cross join acm_mas_base_account cv"
                            + " LEFT JOIN IcmMasContact c ON r.contact_seq = c.ConId"
                            + " LEFT JOIN acm_mas_account acc1 ON cv.personal_tax_account_seq = acc1.account_seq"
                            + " LEFT JOIN acm_mas_account acc2 ON cv.company_tax_account_seq = acc2.account_seq"
                            + " WHERE r.receive_seq = @ref_seq"
                            // income
                            + " union all"

                            + " SELECT case r.receive_mode"
                                + " when 1 then case when mr.event_seq = 16 then cv.loan_account_seq"
                                    + " else cv.over_account_seq"
                                    + " end"
                                + " when 2 then rt.account_seq"
                                + " when 3 then cv.wait_income_bb_account_seq"
                                + " end as account_seq"
                            + ", case r.receive_mode"
                                + " when 1 then case when mr.event_seq = 16 then acc4.account_code"
                                    + " else acc1.account_code"
                                    + " end"
                                + " when 2 then acc2.account_code"
                                + " when 3 then acc3.account_code"
                                + " end as account_code"
                            + ", case r.receive_mode"
                                + " when 1 then case when mr.event_seq = 16 then acc4.account_name"
                                    + " else acc1.account_name"
                                    + " end"
                                + " when 2 then acc2.account_name"
                                + " when 3 then acc3.account_name"
                                + " end as account_name"
                            + ", 'Cr' as dr_or_cr"
                            + ", null as amount_dr"
                            + ", r.amount - coalesce(r.vat_amount, 0) as amount_cr"
                            + " FROM fim_receives r"
                            + " LEFT JOIN fim_money_requests mr on r.money_req_seq = mr.money_req_seq"
                            + " cross join acm_mas_base_account cv"
                            + " LEFT JOIN fim_mas_receive_types rt on r.receive_type_seq = rt.receive_type_seq"
                            + " LEFT JOIN acm_mas_account acc1 ON cv.over_account_seq = acc1.account_seq"
                            + " LEFT JOIN acm_mas_account acc2 ON rt.account_seq = acc2.account_seq"
                            + " LEFT JOIN acm_mas_account acc3 ON cv.wait_income_bb_account_seq = acc3.account_seq"
                            + " LEFT JOIN acm_mas_account acc4 ON cv.loan_account_seq = acc4.account_seq"
                            + " WHERE r.receive_seq = @ref_seq"
                            // tax
                            + " union all"

                            + " SELECT acc1.account_seq as account_seq"
                            + ", acc1.account_code as account_code"
                            + ", acc1.account_name as account_name"
                            + ", 'Cr' as dr_or_cr"
                            + ", null as amount_dr"
                            + ", r.vat_amount as amount_cr"
                            + " FROM fim_receives r"
                            + " cross join acm_mas_base_account cv"
                            + " LEFT JOIN acm_mas_account acc1 ON cv.sale_vat_account_seq = acc1.account_seq"
                            + " WHERE r.receive_seq = @ref_seq";
                        break;
                    #endregion
                    #region fim_money_returns
                    case "fim_money_returns":
                        strSQL = "SELECT exp.account_seq"
                            + ", acc1.account_code"
                            + ", acc1.account_name"
                            + ", 'Dr' as dr_or_cr"
                            + ", sum(coalesce(retd.amount, 0) - coalesce(retd.vat_amount, 0)) as amount_dr"
                            + ", null as amount_cr"
                            + " FROM fim_money_returns ret"
                            + " LEFT JOIN fim_money_return_dtls retd on ret.return_seq = retd.return_seq"
                                + " and retd.record_status not like 'D'"
                            + " LEFT JOIN fim_money_request_dtls mrd on retd.money_req_dtls_seq = mrd.money_req_dtls_seq"
                            + " LEFT JOIN bgm_project_operation_expense opexp on mrd.bgm_proj_oper_exp_seq = opexp.bgm_proj_oper_exp_seq"
                            + " LEFT JOIN bgm_project_expense pjexp on opexp.bgm_proj_exp_seq = pjexp.bgm_proj_exp_seq"
                            + " LEFT JOIN bgm_mas_expense exp on pjexp.bgm_exp_seq = exp.bgm_exp_seq"
                            + " LEFT JOIN acm_mas_account acc1 ON exp.account_seq = acc1.account_seq"
                            + " WHERE ret.return_seq = @ref_seq"
                            + " group by exp.account_seq, acc1.account_code, acc1.account_name"

                            + " union all"

                            + " SELECT acc1.account_seq"
                            + ", acc1.account_code"
                            + ", acc1.account_name"
                            + ", 'Dr' as dr_or_cr"
                            + ", sum(coalesce(retd.vat_amount, 0)) as amount_dr"
                            + ", null as amount_cr"
                            + " FROM fim_money_returns ret"
                            + " LEFT JOIN fim_money_return_dtls retd on ret.return_seq = retd.return_seq"
                                + " and retd.record_status not like 'D'"
                            + " cross JOIN acm_mas_base_account ba"
                            + " LEFT JOIN acm_mas_account acc1 ON ba.buy_vat_account_seq = acc1.account_seq"
                            + " WHERE ret.return_seq = @ref_seq"
                            + " group by acc1.account_seq, acc1.account_code, acc1.account_name"

                            + " union all"

                            + " SELECT ba.loan_account_seq as account_seq"
                            + ", acc1.account_code"
                            + ", acc1.account_name"
                            + ", 'Cr' as dr_or_cr"
                            + ", null as amount_dr"
                            + ", retd.amount as amount_cr"
                            + " FROM fim_money_returns ret"
                            + " LEFT JOIN (select retd.return_seq"
                                + ", sum(coalesce(retd.amount, 0)) as amount"
                                + " from fim_money_return_dtls retd"
                                + " where retd.record_status not like 'D'"
                                + " group by retd.return_seq"
                                + ") retd on ret.return_seq = retd.return_seq"
                            + " CROSS JOIN acm_mas_base_account ba"
                            + " LEFT JOIN acm_mas_account acc1 ON ba.loan_account_seq = acc1.account_seq"
                            + " WHERE ret.return_seq = @ref_seq";
                        break;
                    #endregion
                    #region icm_delivery_note
                    case "icm_delivery_note":
                        //strSQL = "Select coalesce(ChkTaxInvoice, 1) as ChkTaxInvoice"
                        //    + " from IcmDeliveryNote"
                        //    + " where DnId = @ref_seq";
                        //bool ChkTaxInvoice = Convert.ToBoolean(dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows[0][0]);

                        strSQL = "select account_seq, account_code, account_name, dr_or_cr"
                            + ", sum(coalesce(amount_dr, 0)) as amount_dr"
                            + ", null as amount_cr"
                            + @" from (SELECT case when coalesce(ni.HaveItem, 1) = 1 
	then case when ri.ResItemId is not null and rg.ResTypeId <> 1
		then rg.AssetAccountId
		else ex.account_seq
		end
	else acc3.account_seq end as account_seq
, case when coalesce(ni.HaveItem, 1) = 1 
	then case when ri.ResItemId is not null and rg.ResTypeId <> 1
		then acc1.account_code
		else acc2.account_code
		end
	else acc3.account_code end as account_code
, case when coalesce(ni.HaveItem, 1) = 1 
	then case when ri.ResItemId is not null and rg.ResTypeId <> 1
		then acc1.account_name
		else acc2.account_name
		end
	else acc3.account_name end as account_name"
                            + ", 'Dr' as dr_or_cr"
                            + ", coalesce(ni.NetAmount, 0) - COALESCE (ni.VatAmount, 0) as amount_dr"
                            + ", null as amount_cr"

                            + " FROM IcmDeliveryNoteItem ni"
                            + " left join IcmPurchaseOrderInstallmentItem ii on ni.PoInstallItemId = ii.PoInstallItemId"
                            + " left join IcmBidderQuotation q on ii.BidderQuoId = q.BidderQuoId"
                            + " left join IcmPurchaseRequestItem ri on q.PrItemId = ri.PrItemId"
                            + " left join bgm_mas_expense ex on ri.BgExpId = ex.bgm_exp_seq"
                            + " left join IcmMasResourceItem mi on ri.ResItemId = mi.ResItemId"
                            + " left join IcmMasResourceSubGroup sg on mi.ResSubGrpId = sg.ResSubGrpId"
                            + " left join IcmMasResourceGroup rg on sg.ResGrpId = rg.ResGrpId"
                            + " CROSS JOIN acm_mas_base_account ba"
                            + " LEFT JOIN acm_mas_account acc1 ON rg.AssetAccountId = acc1.account_seq"
                            + " LEFT JOIN acm_mas_account acc2 ON ex.account_seq = acc2.account_seq"
                            + " LEFT JOIN acm_mas_account acc3 ON ba.wait_account_seq = acc3.account_seq"
                            + " WHERE ni.DnId = @ref_seq"
                            + ") t"
                            + " group by account_seq, account_code, account_name, dr_or_cr"

                            + " union all"
                            + " select ba.suspense_input_tax_account_seq as account_seq"
                            + ", acc2.account_code as account_code"
                            + ", acc2.account_name as account_name"
                            + ", 'Dr' as dr_or_cr"
                            + ", sum(COALESCE(ni.VatAmount, 0)) as amount_dr"
                            + ", null as amount_cr"
                            + " FROM IcmDeliveryNoteItem ni"
                            + " inner join IcmDeliveryNote dn on ni.DnId = dn.DnId"
                            + " cross join acm_mas_base_account ba"
                            + " LEFT JOIN acm_mas_account acc2 ON ba.suspense_input_tax_account_seq = acc2.account_seq"
                            + " WHERE ni.DnId = @ref_seq"
                            + " group by ba.suspense_input_tax_account_seq, acc2.account_code, acc2.account_name"

                            + " union all"

                            + " SELECT bacc.direct_pay_account_seq as account_seq"
                            + ", acc1.account_code"
                            + ", acc1.account_name"
                            + ", 'Cr' as dr_or_cr"
                            + ", null as amount_dr"
                            + ", sum(coalesce(ni.NetAmount, 0)) as amount_cr"

                            + " FROM IcmDeliveryNoteItem ni"
                            + " cross join acm_mas_base_account bacc"
                            + " LEFT JOIN acm_mas_account acc1 ON bacc.direct_pay_account_seq = acc1.account_seq"
                            + " WHERE ni.DnId = @ref_seq"
                            + " GROUP BY bacc.direct_pay_account_seq, acc1.account_code, acc1.account_name";
                        break;
                    #endregion
                    #region fim_transfers
                    case "fim_transfers":
                        strSQL = "select facc.bgm_source_seq as from_bgm_source_seq"
                            + ", tacc.bgm_source_seq as to_bgm_source_seq"
                            + " from fim_transfers tf"
                            + " left join fim_mas_bank_accounts facc on tf.from_bank_acc_seq = facc.bank_acc_seq"
                            + " left join fim_mas_bank_accounts tacc on tf.to_bank_acc_seq = tacc.bank_acc_seq"
                            + " where tf.transfer_seq = " + ref_seq;
                        dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];

                        if (dtb.Rows.Count > 0)
                        {
                            if (!string.IsNullOrEmpty(dtb.Rows[0][0].ToString()) && !string.IsNullOrEmpty(dtb.Rows[0][1].ToString()) && dtb.Rows[0][0].ToString() != dtb.Rows[0][1].ToString())
                            {
                                strSQL = "SELECT ba.cross_budget_expense_account_seq as account_seq"
                                    + ", acc.account_code"
                                    + ", acc.account_name"
                                    + ", 'Dr' as dr_or_cr"
                                    + ", coalesce(t.amount, 0) as amount_dr"
                                    + ", null as amount_cr"
                                    + " FROM fim_transfers t"
                                    + " CROSS JOIN acm_mas_base_account ba"
                                    + " LEFT JOIN acm_mas_account acc ON ba.cross_budget_expense_account_seq = acc.account_seq"

                                    + " WHERE t.transfer_seq = @ref_seq"

                                    + " union all"

                                    + " SELECT bacc1.account_seq as account_seq"
                                    + ", acc1.account_code"
                                    + ", acc1.account_name"
                                    + ", 'Cr' as dr_or_cr"
                                    + ", null as amount_dr"
                                    + ", coalesce(t.amount, 0) as amount_cr"
                                    + " FROM fim_transfers t"
                                    + " LEFT JOIN fim_mas_bank_accounts bacc1 on t.from_bank_acc_seq = bacc1.bank_acc_seq"
                                    + " LEFT JOIN acm_mas_account acc1 ON bacc1.account_seq = acc1.account_seq"

                                    + " WHERE t.transfer_seq = @ref_seq";
                            }
                            else
                            {
                                strSQL = "SELECT case t.transfer_type"
                                        + " when '1' then acc2.account_seq"
                                        + " when '2' then acc3.account_seq"
                                        + " when '3' then acc2.account_seq"
                                        + " else 0"
                                        + " end as account_seq"
                                    + ", case t.transfer_type"
                                        + " when '1' then acc2.account_code"
                                        + " when '2' then acc3.account_code"
                                        + " when '3' then acc2.account_code"
                                        + " else ''"
                                        + " end as account_code"
                                    + ", case t.transfer_type"
                                        + " when '1' then acc2.account_name"
                                        + " when '2' then acc3.account_name"
                                        + " when '3' then acc2.account_name"
                                        + " else ''"
                                        + " end as account_name"
                                    + ", 'Dr' as dr_or_cr"
                                    + ", coalesce(t.amount, 0) as amount_dr"
                                    + ", null as amount_cr"
                                    + " FROM fim_transfers t"
                                    + " CROSS JOIN acm_mas_base_account ba"
                                    + " LEFT JOIN fim_mas_bank_accounts bacc1 on t.from_bank_acc_seq = bacc1.bank_acc_seq"
                                    + " LEFT JOIN fim_mas_bank_accounts bacc2 on t.to_bank_acc_seq = bacc2.bank_acc_seq"

                                    + " LEFT JOIN acm_mas_account acc1 ON bacc1.account_seq = acc1.account_seq"
                                    + " LEFT JOIN acm_mas_account acc2 ON bacc2.account_seq = acc2.account_seq"
                                    + " LEFT JOIN acm_mas_account acc3 ON ba.cash_account_seq = acc3.account_seq"

                                    + " WHERE t.transfer_seq = @ref_seq"

                                    + " union all"

                                    + " SELECT case t.transfer_type"
                                        + " when '1' then acc3.account_seq"
                                        + " when '2' then acc1.account_seq"
                                        + " when '3' then acc1.account_seq"
                                        + " else 0"
                                        + " end as account_seq"
                                    + ", case t.transfer_type"
                                        + " when '1' then acc3.account_code"
                                        + " when '2' then acc1.account_code"
                                        + " when '3' then acc1.account_code"
                                        + " else ''"
                                        + " end as account_code"
                                    + ", case t.transfer_type"
                                        + " when '1' then acc3.account_name"
                                        + " when '2' then acc1.account_name"
                                        + " when '3' then acc1.account_name"
                                        + " else ''"
                                        + " end as account_name"
                                    + ", 'Cr' as dr_or_cr"
                                    + ", null as amount_dr"
                                    + ", coalesce(t.amount, 0) as amount_cr"
                                    + " FROM fim_transfers t"
                                    + " CROSS JOIN acm_mas_base_account ba"
                                    + " LEFT JOIN fim_mas_bank_accounts bacc1 on t.from_bank_acc_seq = bacc1.bank_acc_seq"
                                    + " LEFT JOIN fim_mas_bank_accounts bacc2 on t.to_bank_acc_seq = bacc2.bank_acc_seq"

                                    + " LEFT JOIN acm_mas_account acc1 ON bacc1.account_seq = acc1.account_seq"
                                    + " LEFT JOIN acm_mas_account acc2 ON bacc2.account_seq = acc2.account_seq"
                                    + " LEFT JOIN acm_mas_account acc3 ON ba.cash_account_seq = acc3.account_seq"

                                    + " WHERE t.transfer_seq = @ref_seq";
                            }
                        }
                        break;
                    #endregion
                    #region fim_cheques
                    case "fim_cheques":
                        strSQL = "SELECT acc.account_seq as account_seq"
                            + ", acc.account_code as account_code"
                            + ", acc.account_name as account_name"
                            + ", 'Dr' as dr_or_cr"
                            + ", coalesce(pc.amount, 0) as amount_dr"
                            + ", null as amount_cr"
                            + " FROM fim_cheques c"
                            + " LEFT JOIN fim_mas_bank_accounts bacc on c.bank_acc_seq = bacc.bank_acc_seq"
                            + " LEFT JOIN acm_mas_account acc ON bacc.account_seq = acc.account_seq"
                            + " left join (select pc.cheque_seq"
                                + ", sum(coalesce(pc.amount, 0)) as amount"
                                + " from fim_payment_cheques pc"
                                + " inner join fim_payments p on pc.pay_seq = p.pay_seq"
                                    + " and p.record_status = 'A'"
                                + " where pc.record_status not like 'D'"
                                + " group by pc.cheque_seq"
                                + ") pc on c.cheque_seq = pc.cheque_seq"
                            + " WHERE c.cheque_seq = @ref_seq"

                            + " union all"

                            + " SELECT acc.account_seq as account_seq"
                            + ", acc.account_code as account_code"
                            + ", acc.account_name as account_name"
                            + ", 'Cr' as dr_or_cr"
                            + ", null as amount_dr"
                            + ", coalesce(pc.amount, 0) as amount_cr"
                            + " FROM fim_cheques c"
                            + " LEFT JOIN fim_mas_bank_accounts bacc on c.bank_acc_seq = bacc.bank_acc_seq"
                            + " LEFT JOIN fim_mas_bank_accounts bacc1 on bacc.saving_bank_acc_seq = bacc1.bank_acc_seq"
                            + " LEFT JOIN acm_mas_account acc ON bacc1.account_seq = acc.account_seq"
                            + " left join (select pc.cheque_seq"
                                + ", sum(coalesce(pc.amount, 0)) as amount"
                                + " from fim_payment_cheques pc"
                                + " inner join fim_payments p on pc.pay_seq = p.pay_seq"
                                    + " and p.record_status = 'A'"
                                + " where pc.record_status not like 'D'"
                                + " group by pc.cheque_seq"
                                + ") pc on c.cheque_seq = pc.cheque_seq"
                            + " WHERE c.cheque_seq = @ref_seq";
                        break;
                    #endregion
                    #region fim_vat
                    case "fim_vat":
                        strSQL = "select * from fim_vat where vat_seq = @ref_seq";
                        DataTable dtbVat = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                        dbManager.Dispose();

                        double dDiffAmount = (cn.ConvertNullDouble(dtbVat.Rows[0]["vat_sale_amount"]) ?? 0) - (cn.ConvertNullDouble(dtbVat.Rows[0]["vat_buy_amount"]) ?? 0);
                        double dPrevAmount = GetPrevVatBuySale(Convert.ToInt32(dtbVat.Rows[0]["month_no"]), Convert.ToInt32(dtbVat.Rows[0]["year_no"]));

                        dbManager.Open();
                        dbManager.CreateParameters(1);
                        dbManager.AddParameters(0, "@ref_seq", ref_seq);

                        strSQL = @"select ba.sale_vat_account_seq as account_seq
, acc.account_code as account_code
, acc.account_name as account_name
, 'Dr' as dr_or_cr
, coalesce(v.vat_sale_amount, 0) as amount_dr
, null as amount_cr
from fim_vat v
cross join acm_mas_base_account ba
left join acm_mas_account acc on ba.sale_vat_account_seq = acc.account_seq
where v.vat_seq = @ref_seq
"
+ (dDiffAmount < 0 ?
@"
union all
select ba.revenue_vat_debtor_account_seq as account_seq
, acc.account_code as account_code
, acc.account_name as account_name
, 'Dr' as dr_or_cr

, " + -dDiffAmount + @"
, null as amount_cr
from acm_mas_base_account ba
left join acm_mas_account acc on ba.revenue_vat_debtor_account_seq = acc.account_seq"
//:
//(dDiffAmount > 0 && dPrevAmount < dDiffAmount ?
//@"
//union all
//select ba.revenue_vat_debtor_account_seq as account_seq
//, acc.account_code as account_code
//, acc.account_name as account_name
//, 'Dr' as dr_or_cr
//
//, " + dDiffAmount + @"
//, null as amount_cr
//from acm_mas_base_account ba
//left join acm_mas_account acc on ba.revenue_vat_debtor_account_seq = acc.account_seq"
: ""
//)
)

+ @"
union all

select ba.buy_vat_account_seq as account_seq
, acc.account_code as account_code
, acc.account_name as account_name
, 'Cr' as dr_or_cr
, null as amount_dr
, coalesce(v.vat_buy_amount, 0) as amount_cr
from fim_vat v
cross join acm_mas_base_account ba
left join acm_mas_account acc on ba.buy_vat_account_seq = acc.account_seq
where v.vat_seq = @ref_seq
"
+ (dDiffAmount > 0 ? (dPrevAmount > dDiffAmount ?
@"
union all
select ba.revenue_vat_debtor_account_seq as account_seq
, acc.account_code as account_code
, acc.account_name as account_name
, 'Cr' as dr_or_cr
, null as amount_dr
, " + dDiffAmount + @"
from acm_mas_base_account ba
left join acm_mas_account acc on ba.revenue_vat_debtor_account_seq = acc.account_seq"
:
@"
union all
select ba.revenue_vat_creditor_account_seq as account_seq
, acc.account_code as account_code
, acc.account_name as account_name
, 'Cr' as dr_or_cr
, null as amount_dr
, " + (dDiffAmount - dPrevAmount) + @"
from acm_mas_base_account ba
left join acm_mas_account acc on ba.revenue_vat_creditor_account_seq = acc.account_seq

union all
select ba.revenue_vat_debtor_account_seq as account_seq
, acc.account_code as account_code
, acc.account_name as account_name
, 'Cr' as dr_or_cr
, null as amount_dr
, " + dPrevAmount + @"
from acm_mas_base_account ba
left join acm_mas_account acc on ba.revenue_vat_debtor_account_seq = acc.account_seq
")
:
@"

"
)
;
                        break;
                    #endregion
                }

                if (strSQL.Length > 0)
                {
                    dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];

                    if (dtb.Rows.Count > 0)
                    {
                        foreach (DataRow dr in dtb.Rows)
                        {
                            if ((cn.ConvertNullInt(dr["amount_dr"]) != null && cn.ConvertNullInt(dr["amount_dr"]) != 0 && dr["dr_or_cr"].ToString() == "Dr")
                                || (cn.ConvertNullInt(dr["amount_cr"]) != null && cn.ConvertNullInt(dr["amount_cr"]) != 0 && dr["dr_or_cr"].ToString() == "Cr"))
                            {
                                M_ACM_GENERATE_ACCOUNT_Auto info = new M_ACM_GENERATE_ACCOUNT_Auto();
                                info.account_seq = cn.ConvertNullInt(dr["account_seq"]);
                                info.dr_or_cr = dr["dr_or_cr"].ToString();
                                info.account_code = dr["account_code"].ToString();
                                info.account_name = dr["account_name"].ToString();
                                info.amount_dr = cn.ConvertNullDouble(dr["amount_dr"]);
                                info.amount_cr = cn.ConvertNullDouble(dr["amount_cr"]);
                                infoList.Add(info);
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }
            return infoList;
        }

        public Boolean CheckRefType(string ref_type)
        {
            Boolean pRef_type = false;
            if (ref_type != "")
            {
                DataRowCollection drc = null;
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@ref_type", ref_type);

                try
                {
                    dbManager.Open();
                    string strSQL = " SELECT COUNT(ref_type) as count_ref_type "
                                   + " FROM acm_transection_group "
                                   + " WHERE ref_type = @ref_type ";

                    drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;

                    if (drc.Count > 0)
                    {
                        if (Convert.ToInt32(drc[0]["count_ref_type"].ToString()) > 0)
                            pRef_type = false;
                        else
                            pRef_type = true;
                    }
                }
                catch (Exception ex)
                {
                    throw (ex);
                }
                finally
                {
                    dbManager.Dispose();
                }
            }
            else
                pRef_type = false;

            return pRef_type;
        }

        public Int32 MaxTransactionGroup()
        {
            DataRowCollection drc = null;
            Int32 tran_group_code = 0;
            try
            {
                dbManager.Open();
                string strSQL = " SELECT COUNT(tran_grp_seq) as Max "
                              + " FROM acm_transection_group ";

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;

                if (drc.Count > 0)
                {
                    tran_group_code = Convert.ToInt32(drc[0]["Max"]);
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }
            return tran_group_code;
        }

        public void InsertTransactionGroup(M_ACM_GENERATE_ACCOUNT_Auto info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(5);
                dbManager.AddParameters(0, "@tran_grp_code", info.tran_grp_code);
                dbManager.AddParameters(1, "@ref_type", info.ref_type);
                dbManager.AddParameters(2, "@record_status", info.record_status);
                dbManager.AddParameters(3, "@created_by", info.created_by);
                dbManager.AddParameters(4, "@created_when", info.created_when);


                strSQL = "INSERT INTO acm_transection_group "
                    + " (tran_grp_code,ref_type,record_status "
                    + ", created_by,created_when)"
                    + "  VALUES("
                    + "  @tran_grp_code"
                    + ", @ref_type"
                    + ", @record_status"
                    + ", @created_by"
                    + ", @created_when"
                    + " )";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

        }

        public void UpdateRefType(string ref_type, int ref_seq, int tran_grp_seq)
        {
            string strSQL = "";
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);
                //dbManager.AddParameters(0, "@ref_type", info.ref_type);
                dbManager.AddParameters(0, "@tran_grp_seq", tran_grp_seq);
                dbManager.AddParameters(1, "@ref_seq", ref_seq);

                switch (ref_type)
                {
                    case "fim_petitions":
                        strSQL = " UPDATE " + ref_type
                                + " SET tran_grp_seq=@tran_grp_seq "
                                + " WHERE petition_seq=@ref_seq";
                        break;
                    case "fim_money_requests":
                        strSQL = " UPDATE " + ref_type
                                + " SET tran_grp_seq=@tran_grp_seq "
                                + " WHERE money_req_seq=@ref_seq";
                        break;
                    case "fim_payments":
                        strSQL = " UPDATE " + ref_type
                                + " SET tran_grp_seq=@tran_grp_seq "
                                + " WHERE pay_seq=@ref_seq";
                        break;
                    case "fim_receives":
                        strSQL = " UPDATE " + ref_type
                                + " SET tran_grp_seq=@tran_grp_seq "
                                + " WHERE receive_seq=@ref_seq";
                        break;
                    case "fim_money_returns":
                        strSQL = " UPDATE " + ref_type
                                + " SET tran_grp_seq=@tran_grp_seq "
                                + " WHERE return_seq=@ref_seq";
                        break;
                    case "icm_delivery_note":
                        strSQL = " UPDATE IcmDeliveryNote"
                                + " SET TranGrpId=@tran_grp_seq "
                                + " WHERE DnId=@ref_seq";
                        break;
                    case "fim_transfers":
                        strSQL = " UPDATE fim_transfers"
                                + " SET tran_grp_seq=@tran_grp_seq "
                                + " WHERE transfer_seq=@ref_seq";
                        break;
                    case "fim_cheques":
                        strSQL = " UPDATE fim_cheques"
                                + " SET tran_grp_seq=@tran_grp_seq "
                                + " WHERE cheque_seq=@ref_seq";
                        break;
                    case "fim_vat":
                        strSQL = " UPDATE fim_vat"
                                + " SET tran_grp_seq=@tran_grp_seq "
                                + " WHERE vat_seq=@ref_seq";
                        break;
                }

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public double GetPrevVatBuySale(int month, int year)
        {
            string strSQL;
            if (month == 1)
            {
                month = 1;
                year--;
            }
            else
            {
                month--;
            }

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);
                dbManager.AddParameters(0, "@month", month);
                dbManager.AddParameters(1, "@year", year);

                strSQL = "select v.next_month_vat_amount"
                    + " from fim_vat v"
                    + " where v.record_status <> 'D'"
                    + " and v.year_no = @year"
                    + " and v.month_no = @month";

                var dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                if (dtb.Rows.Count > 0)
                {
                    return cn.ConvertNullDouble(dtb.Rows[0][0]) ?? 0;
                }
                else
                {
                    return 0;
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }
        }
    }
}
